package org.yoao.crm.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.yoao.crm.DBManager.DBManager;
import org.yoao.crm.bean.Nac;

/**
 * @author yxwonline.com
 * @description 
 * @date 2010-8-13 下午07:08:44
 */
public class NacDAO extends DBManager  {
	private static final Logger log = Logger.getLogger(NacDAO.class);
	
	public int insert(Nac bean)throws Exception{
		String sql = "insert into "+bean.getTableName()+"(custName, mobile, email, city, createTime, " +
					 "isFlag, memo, userSno) values('"+bean.getCustName()+"', '"+bean.getMobile()+"', '"+
					 bean.getEmail()+"', '"+bean.getCity()+"', '"+bean.getCreateTime()+"', 0, NULL, NULL)";
		return update(sql);
	}
	
	public int modifyMemo(Nac bean)throws Exception{
		String sql = "update "+bean.getTableName()+" set memo='"+bean.getMemo()+"' where id="+bean.getId();
		return update(sql);
	}
	
	public int modifyIsFlag(String tableName, String id, String isFlag) throws Exception{
		String sql = "update "+tableName+" set isFlag="+isFlag+" where id in("+id+")";
		return update(sql);
	}
	
	public int delete(String id, String tableName)throws Exception{
		String sql = "delete "+tableName+" where id in("+id+")";
		return update(sql);
	}
	
	public int getDataCount(Map map) throws Exception{
		String tableName = (String)map.get("TABLENAME");		
		String sql = "select count(id) as nacCount from " + tableName;
		String where = " where ";
		if(map != null && !map.isEmpty()){
			Iterator iter = map.keySet().iterator();
			while(iter.hasNext()){
				String key = iter.next().toString();
				if(key.equals("TABLENAME")) continue;
				String value = map.get(key).toString();
				if(key.indexOf(" LIKE ") < 1){
					where = where + key + "='" + value + "' and ";			
				}else{
					where = where + key + " '%" + value + "%' and ";
				}
			}
		}
		if(where.endsWith(" and ")) where = where.substring(0, where.length()-4);
		if(where.equals(" where ")) where = "";
		sql += where;
		String result = querySingleValue(sql);
		if(result == null || result.trim().equals("")) result = "0";
		return Integer.parseInt(result);
	}
	
	public List find(int startRow, int endRow, Map map) throws Exception{
		String tableName = (String)map.get("TABLENAME");
		String sql = "select top "+endRow+" "+tableName+".id, "+tableName+".custName, "+tableName+".mobile, " +
					 tableName+".email, "+tableName+".city, "+tableName+".createTime, " +tableName+".isFlag, " +
					 tableName+".memo, baseUser.nickname as userSno from " + tableName +
					 " left join baseuser on baseuser.userName="+tableName+".userSno " +
					 " where "+tableName+".id not in(";
		String filterSql = "select top "+startRow+" n2.id from "+tableName+" as n2 ";
		
		String where = "";
		String fwhere = "";
		if(map != null && !map.isEmpty()){
			Iterator iter = map.keySet().iterator();
			while(iter.hasNext()){
				String key = iter.next().toString();
				if(key.equals("TABLENAME")) continue;
				String value = map.get(key).toString();
				if(key.indexOf(" LIKE ") < 1){
					where = where + tableName+"." + key + "='" + value + "' and ";
					fwhere = fwhere + "n2." + key + "='" + value + "' and ";					
				}else{
					where = where + tableName+"." + key + " '%" + value + "%' and ";
					fwhere = fwhere + "n2." + key + " '%" + value + "%' and ";
				}
			}
		}
		if(where.endsWith(" and ")) where = where.substring(0, where.length()-4);
		if(where.equals("")) where = null;
		if(fwhere.endsWith(" and ")) fwhere = fwhere.substring(0, fwhere.length()-4);
		if(fwhere.equals("")) fwhere = null;
		
		if(where == null){
			sql = sql + filterSql + " order by n2.id desc) order by "+tableName+".id desc";
		}else{
			filterSql = filterSql + " where " + fwhere;
			sql = sql + filterSql + " order by n2.id desc) and " + where + " order by "+tableName+".id desc";
		}
		return query(sql);
	}
	
	private List query(String sql){
		log.debug(sql);
		Connection con = null;
		ResultSet rs = null;
		Statement stmt = null;
		List result = new ArrayList();						
		try{
			con = getConnection();
			stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			rs = stmt.executeQuery(sql);	
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			String columnName = null;
			while(rs.next()){
				Nac bean = new Nac();
				for(int i=1;i<=columnCount;i++){					
					columnName = rsmd.getColumnName(i);					
					if(columnName.equalsIgnoreCase("ID"))
						bean.setId(rs.getInt(columnName));
					if(columnName.equalsIgnoreCase("CUSTNAME"))
						bean.setCustName(rs.getString(columnName));
					if(columnName.equalsIgnoreCase("MOBILE"))
						bean.setMobile(rs.getString(columnName));
					if(columnName.equalsIgnoreCase("EMAIL"))	
						bean.setEmail(rs.getString(columnName));
					if(columnName.equalsIgnoreCase("CITY"))					
						bean.setCity(rs.getString(columnName));
					if(columnName.equalsIgnoreCase("CREATETIME"))
						bean.setCreateTime(rs.getString(columnName));
					if(columnName.equalsIgnoreCase("ISFLAG"))
						bean.setIsFlag(rs.getInt(columnName));
					if(columnName.equalsIgnoreCase("MEMO"))
						bean.setMemo(rs.getString(columnName));
					if(columnName.equalsIgnoreCase("USERSNO"))
						bean.setUserName(rs.getString(columnName));
				}
		    	result.add(bean);
		    }
			rs.close();
			stmt.close();
		}
		catch(SQLException e){
			result = null;
			log.error("ERROR SQL:"+sql);
			e.printStackTrace();
		}
		finally{
			closeConnection(con);
			return result;
	    }		
	}
}